
from datetime import datetime, timedelta
import numpy as np
import pandas as pd
from fuzzywuzzy import fuzz
from pandas.core.frame import DataFrame
from pandas.core.groupby import DataFrameGroupBy

today = datetime.now().date()
tomorrow = today + timedelta(days=1)
current_year = datetime.now().year
first_day_of_year = datetime(current_year, 1, 1).date()

df = pd.read_excel(rf'D:\Desktop\第一季度新开工\第一季度开工项目情况表_2025_03_12_16_17_37.xlsx',sheet_name='主表',header=0)




df['区县']=df['区县'].fillna('本级')
df['计划入库时间']=df['计划入库时间'].fillna('无')


tz_500 = df.loc[df['总投资'] < 500]
year_gcl_0 = df.loc[df['全年计划完成工程量'] <= 0]
finish_gcl_0 = df.loc[df['第一季度计划完成工程量'] <= 0]
all_gcl_0 = df.loc[df['累计完成实物工程量'] <= 0]
ac_kg_date = df[df.apply(lambda row: datetime.strptime(row['实际开工时间'], "%Y-%m-%d").date() < first_day_of_year or datetime.strptime(row['实际开工时间'], "%Y-%m-%d").date() > tomorrow , axis=1)]
ac_rk_date = df.loc[df['计划入库时间'] == '无' ]

count = df.groupby(by=['市州','区县'],dropna=False).size().rename('统计')
tz_500_count = tz_500.groupby(by=['市州','区县'],dropna=False).size().rename('总投资小于500')
year_gcl_0_count = year_gcl_0.groupby(by=['市州','区县'],dropna=False).size().rename('全年计划完成工程量为0')
finish_gcl_0_count = finish_gcl_0.groupby(by=['市州','区县'],dropna=False).size().rename('第一季度计划完成工程量为0')
all_gcl_0_count = all_gcl_0.groupby(by=['市州','区县'],dropna=False).size().rename('累计完成实物工程量为0')
ac_kg_date_count = ac_kg_date.groupby(by=['市州','区县'],dropna=False).size().rename(f'实际开工时间不在{current_year}和{tomorrow}之间')
ac_rk_count = ac_rk_date.groupby(by=['市州','区县'],dropna=False).size().rename('入库时间为空')


# result = pd.concat([tz_500_count,year_gcl_0_count,finish_gcl_0_count,all_gcl_0_count,ac_kg_date_count], axis=1, join='outer',levels=2)

# result = result.groupby(by=['市州','区县'],dropna=False)

print(all_gcl_0_count)

result = pd.merge(right=tz_500_count,left=year_gcl_0_count,how="outer",on=['市州','区县'])
result = pd.merge(right=result,left=finish_gcl_0_count,how="outer",on=['市州','区县'])
result = pd.merge(right=result,left=all_gcl_0_count,how="outer",on=['市州','区县'])
result = pd.merge(right=result,left=ac_kg_date_count,how="outer",on=['市州','区县'])
result = pd.merge(right=result,left=ac_rk_count,how="outer",on=['市州','区县'])





# result = pd.merge()

# result.set_index(keys=['市州','区县'])
result.to_excel(rf'D:\Desktop\第一季度新开工\第一季度开工统计问题.xlsx')





